package util;

import bean.TourRoute;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import static org.apache.commons.lang.StringUtils.isNumeric;

public class TourRouteDao {
    //增加
    public void addTourRoute(TourRoute tr) throws SQLException {
        Connection conn = DbUtil.getConnection();
        tr.setImageURL("images/鼎龙湾.jpg");
        //sql
        String sql="insert into TourRoute"+"(imageURL,routeNo,startPoint,endPoint,days,attraction)"+"values("+"'"+tr.getImageURL()+"'"+","+"'"+tr.getRouteNo()+"'"+","+"'"+tr.getStartPoint()+"'"+","+"'"+tr.getEndPoint()+"'"+","+"'"+tr.getDays()+"'"+","+"'"+tr.getAttraction()+"'"+")";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ptmt.execute();
        //更新编号
        ptmt = conn.prepareStatement("UPDATE UPDATNO set routeNo='"+tr.getRouteNo()+"' WHERE No='1'");//更新
        ptmt.execute();

        System.out.println("add tourRoute success!");
    }

    //删除(routeNo)
    public void delTourRoute(String routeNo) throws SQLException {
        Connection conn = DbUtil.getConnection();
        String sql = "delete from TourRoute where routeNo=?";
        //预编译；预编译SQL,减少sql执行
        PreparedStatement ptmt = conn.prepareStatement(sql);
        //传参
        ptmt.setString(1,routeNo);
        ptmt.execute();
    }

    //更新
    public void updateTourRoute(String routeNo,String startPoint,String endPoint,int days,String attraction) throws SQLException {
        Connection conn = DbUtil.getConnection();
        String sql = "UPDATE TourRoute set startPoint='"+startPoint+"',endPoint='"+endPoint+"',days='"+days+"',attraction='"+attraction+"' where routeNo='"+routeNo+"'";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ptmt.execute();
    }

    //查(routeNo)
    public TourRoute get(String routeNo) throws SQLException {
        TourRoute tr = null;
        Connection conn = DbUtil.getConnection();
        String sql = "select * from  TourRoute where routeNo='"+routeNo+"'";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ResultSet rs = ptmt.executeQuery();
        while (rs.next()){
            tr = new TourRoute();
            tr.setImageURL(rs.getString("imageURL"));
            tr.setRouteNo(rs.getString("routeNo"));
            tr.setStartPoint(rs.getString("startPoint"));
            tr.setEndPoint(rs.getString("endPoint"));
            tr.setDays(rs.getInt("days"));
            tr.setAttraction(rs.getString("attraction"));
        }
        return tr;
    }

    //列出所有路线
    public List<TourRoute> query() throws SQLException {
        Connection conn = DbUtil.getConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM TourRoute");

        List<TourRoute> TRList = new ArrayList<TourRoute>();
        TourRoute tr = null;
        while (rs.next()){
            tr = new TourRoute();
            tr.setImageURL(rs.getString("imageURL"));
            tr.setRouteNo(rs.getString("routeNo"));
            tr.setStartPoint(rs.getString("startPoint"));
            tr.setEndPoint(rs.getString("endPoint"));
            tr.setDays(rs.getInt("days"));
            tr.setAttraction(rs.getString("attraction"));

            TRList.add(tr);
        }

        return TRList;
    }

    //搜索框
    public List<TourRoute> search(String find) throws SQLException {
        Connection conn = DbUtil.getConnection();
        List<TourRoute> TRList = new ArrayList<TourRoute>();
        Statement stmt = conn.createStatement();
        String[] sql = new String[5];
        sql[0] = "select * from  TourRoute where routeNo='"+find+"'";
        sql[1] = "select * from  TourRoute where startPoint='"+find+"'";
        sql[2] = "select * from  TourRoute where endPoint='"+find+"'";
        sql[3] = "select * from  TourRoute where days='"+find+"'";
        sql[4] = "select * from  TourRoute where attraction='"+find+"'";
        boolean flag = isNumeric(find);
        for (int i=0;i<5;i++) {
            if (!flag){      //字符串，sql[3]不可以执行
                if (i==3)
                    continue;
            }
            ResultSet rs = stmt.executeQuery(sql[i]);
            while (rs.next()) {
                TourRoute tr = new TourRoute();
                tr.setRouteNo(rs.getString("routeNo"));
                tr.setStartPoint(rs.getString("startPoint"));
                tr.setEndPoint(rs.getString("endPoint"));
                tr.setDays(rs.getInt("days"));
                tr.setAttraction(rs.getString("attraction"));
                TRList.add(tr);
            }
        }
        return TRList;
    }

    public String updateRouteNo() throws SQLException {
        //获取已存在的最大（新）routeNo
        String routeNo = null;
        Connection conn = DbUtil.getConnection();
        String sql = "select * from  UPDATNO";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ResultSet rs = ptmt.executeQuery();
        while (rs.next()){
            routeNo = rs.getString("routeNo");
        }
        char[] one = routeNo.toCharArray();
        char first = one[0];
        String two = routeNo.substring(1);
        int num = Integer.parseInt(two);
        num = num+1;
        System.out.println(one + "-" + two);
        routeNo = first + Integer.toString(num);

        System.out.println(routeNo);

        return routeNo;
    }
}
